package org.mebusw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.mebusw.dao.IUserDAO;
import org.mebusw.struts.vo.User;

/**
 * mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uid      | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
| password | char(41)    | YES  |     | NULL    |       |
| score    | int(11)     | YES  |     | NULL    |       |
| reg_date | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+ 
 */
public class IUserDAOImpl implements IUserDAO {
	private Connection conn = null;

	public IUserDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doCreate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO user(uid, username, password, score, reg_date) " +
				"VALUES (?,?,PASSWORD(?),?,?)";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setInt(4, user.getScore());
			pstmt.setDate(5, new java.sql.Date(user.getReg_date().getTime()));
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int uid) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM user WHERE uid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	@Override
	public boolean doUpdate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE user SET username=?, password=password(?), score=?, reg_date=?) WHERE uid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setInt(4, user.getScore());
			pstmt.setDate(5, new java.sql.Date(user.getReg_date().getTime()));
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	@Override
	public List<User> findAll(int currentPage, int lineSize, String keyword)
			throws Exception {
		List<User> allUsers = new ArrayList<User>();
		PreparedStatement pstmt = null;
		String sql;

		try {
			/*
			 * //for Oracle statement String sql = "SELECT temp.* FROM " +
			 * "(SELECT uid, username, password, score, reg_date, ROWNUM rn " +
			 * "FROM user " +
			 * "WHERE (uid LIKE upper(?) OR username LIKE upper(?) OR score LIKE upper(?) "
			 * +
			 * "OR to_char(reg_date, 'yyyy') LIKE upper(?) OR to_char(reg_date, 'mm') LIKE upper(?) "
			 * + "OR to_char(reg_date, 'dd') LIKE upper(?)) " //end of WHERE +
			 * "AND ROWNUM <= " + (currPage * pageSize) + ")" //end of 2nd
			 * SELECT + "temp WHERE temp.rn >" + (currPage - 1) * pageSize;
			 */
			// for MySQL statement
			sql = "SET @cnt = 0";
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeQuery();

			sql = "select * from ("
					+ "select (@cnt := @cnt + 1) as rn, uid, username, password, score, reg_date from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) "
					+ ") as temp where " + " temp.rn <= "
					+ (currentPage * lineSize) + " and temp.rn > "
					+ ((currentPage - 1) * lineSize);

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			User user = null;
			//int rn;
			while (rs.next()) {
				user = new User();
				//rn = rs.getInt(1);
				user.setUid(rs.getInt(2));
				user.setUsername(rs.getString(3));
				user.setPassword(rs.getString(4));
				user.setScore(rs.getInt(5));
				user.setReg_date(rs.getDate(6));
				allUsers.add(user);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return allUsers;
	}

	@Override
	public User findById(int uid) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT uid, username, password, score, reg_date FROM user WHERE uid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setScore(rs.getInt(4));
				user.setReg_date(rs.getDate(5));
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return user;
	}

	@Override
	public int getAllCount(String keyword) throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(uid) from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) ";

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

	@Override
	public User findByName(String username, String pwd) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT uid, username, password, score, " +
				"reg_date FROM user WHERE username=? AND password=PASSWORD(?)";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, pwd);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setScore(rs.getInt(4));
				user.setReg_date(rs.getDate(5));
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return user;
	}

}
